CREATE OR REPLACE PROCEDURE pc_stakeholders_proj (pphid IN NUMBER, xmlsource IN VARCHAR2) AUTHID CURRENT_USER  
IS  
    icount INTEGER;  
    e_invalid_xml EXCEPTION;  
BEGIN  
    -- 检查输入参数  
    IF pphid IS NULL OR pphid = 0 THEN  
        RETURN;  
    END IF;  
  
    IF xmlsource IS NULL OR xmlsource = '' THEN  
        RETURN;  
    END IF;  
  
    -- 新增行  
    BEGIN  
        INSERT INTO pc_stakeholders_ls (  
            phid, mstphid, dw, gxr_xm, gxr_role, gxr_level, gxr_office, bxmzy,  
            gxr_salutation, contact, gxr_landline, jyhffs, creator, editor,  
            ng_insert_dt, ng_update_dt  
        )  
        SELECT   
            x.phid, x.mstphid, x.dw, x.gxr_xm, x.gxr_role, x.gxr_level, x.gxr_office,  
            x.bxmzy, x.gxr_salutation, x.contact, x.gxr_landline, x.jyhffs,  
            x.creator, x.editor, x.ng_insert_dt, x.ng_update_dt  
        FROM   
            XMLTABLE(  
                '/pc_stakeholders/table/newRow/row'   
                PASSING XMLTYPE(xmlsource)   
                COLUMNS   
                    phid NUMBER(26,0) PATH 'phid',  
                    mstphid NUMBER(26,0) PATH 'mstphid',  
                    dw VARCHAR2(100) PATH 'dw',  
                    gxr_xm VARCHAR2(100) PATH 'gxr_xm',  
                    gxr_role VARCHAR2(100) PATH 'gxr_role',  
                    gxr_level VARCHAR2(100) PATH 'gxr_level',  
                    gxr_office VARCHAR2(100) PATH 'gxr_office',  
                    bxmzy VARCHAR2(100) PATH 'bxmzy',  
                    gxr_salutation VARCHAR2(100) PATH 'gxr_salutation',  
                    contact VARCHAR2(100) PATH 'contact',  
                    gxr_landline NUMBER(26,2) PATH 'gxr_landline',  
                    jyhffs NUMBER(26,2) PATH 'jyhffs',  
                    creator NUMBER(17) PATH 'creator',  
                    editor NUMBER(17) PATH 'editor',  
                    ng_insert_dt DATE PATH 'ng_insert_dt',  
                    ng_update_dt DATE PATH 'ng_update_dt'  
            ) x;  
  
        SELECT COUNT(*) INTO icount FROM pc_stakeholders_ls;  
        IF icount > 0 THEN  
            UPDATE pc_stakeholders_ls SET mstphid = pphid;  
            DELETE FROM pc_stakeholders WHERE mstphid = pphid;  
            INSERT INTO pc_stakeholders SELECT * FROM pc_stakeholders_ls;  
            DELETE FROM pc_stakeholders_ls;  
        END IF;  
    EXCEPTION  
        WHEN OTHERS THEN  
            RAISE_APPLICATION_ERROR(-20001, 'Error processing new rows: ' || SQLERRM);  
    END;  
  
    -- 修改行  
    BEGIN  
        INSERT INTO pc_stakeholders_ls (  
             phid, mstphid, dw, gxr_xm, gxr_role, gxr_level, gxr_office, bxmzy,  
            gxr_salutation, contact, gxr_landline, jyhffs, creator, editor,  
            ng_insert_dt, ng_update_dt   
        )  
        SELECT   
             x.phid, x.mstphid, x.dw, x.gxr_xm, x.gxr_role, x.gxr_level, x.gxr_office,  
            x.bxmzy, x.gxr_salutation, x.contact, x.gxr_landline, x.jyhffs,  
            x.creator, x.editor, x.ng_insert_dt, x.ng_update_dt   
        FROM   
            XMLTABLE(  
                '/pc_stakeholders/table/modifiedRow/row'   
                PASSING XMLTYPE(xmlsource)   
                COLUMNS   
                    phid NUMBER(26,0) PATH 'phid',  
                    mstphid NUMBER(26,0) PATH 'mstphid',  
                    dw VARCHAR2(100) PATH 'dw',  
                    gxr_xm VARCHAR2(100) PATH 'gxr_xm',  
                    gxr_role VARCHAR2(100) PATH 'gxr_role',  
                    gxr_level VARCHAR2(100) PATH 'gxr_level',  
                    gxr_office VARCHAR2(100) PATH 'gxr_office',  
                    bxmzy VARCHAR2(100) PATH 'bxmzy',  
                    gxr_salutation VARCHAR2(100) PATH 'gxr_salutation',  
                    contact VARCHAR2(100) PATH 'contact',  
                    gxr_landline NUMBER(26,2) PATH 'gxr_landline',  
                    jyhffs NUMBER(26,2) PATH 'jyhffs',  
                    creator NUMBER(17) PATH 'creator',  
                    editor NUMBER(17) PATH 'editor',  
                    ng_insert_dt DATE PATH 'ng_insert_dt',  
                    ng_update_dt DATE PATH 'ng_update_dt'    
            ) x;  
  
        SELECT COUNT(*) INTO icount FROM pc_stakeholders_ls;  
        IF icount > 0 THEN  
            UPDATE pc_stakeholders_ls SET mstphid = pphid;  
            MERGE INTO pc_stakeholders p  
            USING pc_stakeholders_ls l  
            ON (p.phid = l.phid)  
            WHEN MATCHED THEN  
                UPDATE SET   
                    p.mstphid = l.mstphid,  
                    p.dw = l.dw,  
                    p.gxr_xm = l.gxr_xm,  
                    p.gxr_role = l.gxr_role,  
                    p.gxr_level = l.gxr_level,  
                    p.gxr_office = l.gxr_office,  
                    p.bxmzy = l.bxmzy,  
                    p.gxr_salutation = l.gxr_salutation,  
                    p.contact = l.contact,  
                    p.gxr_landline = l.gxr_landline,  
                    p.jyhffs = l.jyhffs,  
                    p.creator = l.creator,  
                    p.editor = l.editor,  
                    p.ng_insert_dt = l.ng_insert_dt,  
                    p.ng_update_dt = l.ng_update_dt;  
            DELETE FROM pc_stakeholders_ls;  
        END IF;  
    EXCEPTION  
        WHEN OTHERS THEN  
            RAISE_APPLICATION_ERROR(-20002, 'Error processing modified rows: ' || SQLERRM);  
    END;  
  
    -- 删除行  
    BEGIN  
        INSERT INTO pc_stakeholders_ls (phid)  
        SELECT x.phid  
        FROM   
            XMLTABLE(  
                '/pc_stakeholders/table/deletedRow/row'   
                PASSING XMLTYPE(xmlsource)   
                COLUMNS   
                    phid NUMBER(26) PATH 'key'  
            ) x;  
  
        SELECT COUNT(*) INTO icount FROM pc_stakeholders_ls;  
        IF icount > 0 THEN  
            DELETE FROM pc_stakeholders WHERE phid IN (SELECT phid FROM pc_stakeholders_ls);  
            DELETE FROM pc_stakeholders_ls;  
        END IF;  
    EXCEPTION  
        WHEN OTHERS THEN  
            RAISE_APPLICATION_ERROR(-20003, 'Error processing deleted rows: ' || SQLERRM);  
    END;  
  
    -- 始终清空临时表（如果需要在每个块之后清空，可以保留在每个END;之后）  
    -- DELETE FROM pc_stakeholders_ls; -- 已在每个块中清空，这里可以省略  
EXCEPTION  
    WHEN e_invalid_xml THEN  
        RAISE_APPLICATION_ERROR(-20000, 'Invalid XML input.');  
    WHEN OTHERS THEN  
        RAISE_APPLICATION_ERROR(-20004, 'An unexpected error occurred: ' || SQLERRM);  
END;  
